﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Windows.Forms;

namespace HSIA
{
    public class DatabaseObject
    {
        public static string sMasterConnect = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}";
        public static DatabaseObject Instance;

        public EventHandle eventHandle;
        public DataTable dtSystemParam;
        public DataTable dtLanguage;
        public DataTable dtMeasureType;
        public DataTable dtSensorTitle;
        public DataTable dtLocationTitle;
        public DataRow drLoginUser;

        public string sXMLDir;
        public string sDBName = "AMATE_MD";

        public DatabaseObject(string sConn)
        {
            if (sConn == "") return;
            sXMLDir = sConn;
            eventHandle = new EventHandle();
            Refresh_AllDt();
            Instance = this;
        }

        public SqlConnection GetMyConnection()
        {
            SqlConnection sc = null;
            try
            {
                sc = new SqlConnection(sXMLDir);
                sc.Open();
            }
            catch (Exception)
            {
            }
            return sc;
        }

        private SqlConnection GetConnection(string sConnect)
        {
            SqlConnection sc = null;
            try
            {
                sc = new SqlConnection(sConnect);
                sc.Open();
            }
            catch (Exception)
            {
            }
            return sc;
        }

        public void RefreshSystemParam()
        {
            dtSystemParam = GetDataTableBySql("select * from Sys_Param", "SystemParam");
            dtSystemParam.PrimaryKey = new DataColumn[] { dtSystemParam.Columns["Name"] };
        }

        public void RefreshLanguage()
        {
            dtLanguage = GetDataTableBySql("select * from Sys_Language", "Language");
            dtLanguage.PrimaryKey = new DataColumn[] { dtLanguage.Columns["ID"] };
        }

        public void RefreshMeasureType()
        {
            dtMeasureType = GetDataTableBySql("select * from Sys_MeasureType order by FieldIndex desc", "MeasureType");
            dtMeasureType.PrimaryKey = new DataColumn[] { dtMeasureType.Columns["ID"] };
            return;
            string dbo_Vag = "";
            string dbo_Measure = "";
            string dbo_pMeasure = "";
            string dbo_W = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                string sWarnType = Convert.ToString(int.Parse(dr["WarnType"].ToString()), 2).PadLeft(4, '0');

                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {

                    string sCnum = (iCnum != 1) ? "_" + i : "";
                    string sColumn = dr["ID"] + sCnum;

                    dbo_Vag += "MAX(" + sColumn + ") as max" + sColumn + ",MIN(" + sColumn + ") as min" + sColumn + ",";

                    dbo_Measure += "{0}" + dr["ID"] + sCnum + "{1},";
                    if (sWarnType[0].ToString() == "1") dbo_pMeasure += "U" + sColumn + ",";
                    if (sWarnType[1].ToString() == "1") dbo_pMeasure += "UP" + sColumn + ",";
                    if (sWarnType[2].ToString() == "1") dbo_pMeasure += "LP" + sColumn + ",";
                    if (sWarnType[3].ToString() == "1") dbo_pMeasure += "L" + sColumn + ",";
                    dbo_W += "0";
                }
            }
            //dbo_Measure += "{0}W ";
            PublicFunc.dbo_Vag = dbo_Vag.TrimEnd(',');
            PublicFunc.dbo_Measure = dbo_Measure.TrimEnd(',');
            PublicFunc.dbo_pMeasure = dbo_pMeasure.TrimEnd(',');
            PublicFunc.dbo_W = dbo_W;
        }

        public void Refresh_AllDt()
        {
            RefreshLanguage();
            RefreshSystemParam();
            RefreshMeasureType();
        }

        public string GetLanguage(object oKey)
        {
            if (oKey == null || oKey == DBNull.Value || oKey.ToString() == "") return "";
            int iKey = 0;
            if (!int.TryParse(oKey.ToString(), out iKey)) return "";
            string strGetName = "";

            if (dtLanguage != null && dtLanguage.Rows.Count > 0)
            {
                DataRow drLanguage = dtLanguage.Rows.Find(iKey);
                if (drLanguage != null) strGetName = drLanguage[PublicFunc.sLanguage].ToString();
            }
            return strGetName;
        }

        /// <summary>
        /// oKey为搜索键值， iType：1测量名称，2测量单位，3测量名称（单位）
        /// </summary>
        /// <returns></returns>
        public string GetMeasureName(string sKey, int iType)
        {
            if (sKey == null || sKey.ToString() == "") return "";
            string strGetName = "";

            if (dtMeasureType.Rows.Count > 0)
            {
                DataRow drMeasureType = dtMeasureType.Rows.Find(sKey);
                if (drMeasureType != null)
                {
                    switch (iType)
                    {
                        case 0:
                            strGetName = drMeasureType["ID"].ToString();
                            break;
                        case 1:
                            strGetName = drMeasureType["Name"].ToString();
                            break;
                        case 2:
                            strGetName = drMeasureType["Unit"].ToString();
                            break;
                        case 3:
                            strGetName = drMeasureType["Name"] + "(" + drMeasureType["Unit"] + ")";
                            break;
                    }
                }
            }
            return strGetName;
        }

        public string GetMeasureID(string sKey, string sValue)
        {
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                if (dr[sKey].ToString() == sValue)
                    return dr["ID"].ToString();
            }
            return "";
        }

        public double GetMeasureValue(string sKey, string sName)
        {
            double dValue = double.MaxValue;
            if (sKey == null || sKey.ToString() == "") return dValue;

            if (dtMeasureType.Rows.Count > 0)
            {
                DataRow drMeasureType = dtMeasureType.Rows.Find(sKey);
                if (drMeasureType != null)
                {
                    if (!double.TryParse(drMeasureType[sName].ToString(), out dValue)) return dValue;
                }
            }
            return dValue;
        }

        public DataTable GetDataTableBySql(string strSQL, string strName)
        {
            if (strSQL == "") return null;
            DataTable dt = new DataTable(strName);
            SqlDataAdapter da = null;
            SqlConnection sc = GetMyConnection();

            try
            {
                if (strSQL.Trim() == "")
                    da = new SqlDataAdapter("select * from " + strName, sc);
                else
                {
                    SqlCommand cmd = new SqlCommand(strSQL, sc);
                    cmd.CommandTimeout = 60;
                    da = new SqlDataAdapter(cmd);
                }

                da.Fill(dt);
            }
            catch (Exception e)
            {
                SqlConnection.ClearAllPools();
                TransactionError(sc.ConnectionString, "获取基础表:" + strSQL, e);
                //MyErrorLog.WinLog(e.Message + "\r\n(" + strSQL + GetLanguage(119) + ")");
            }
            finally
            {
                da.Dispose();
                sc.Close();
            }
            return dt;
        }

        public DataTable GetDataTableBySql(string strSQL, string strName, string strConnect)
        {
            if (strSQL == "") return null;
            DataTable dt = new DataTable(strName);
            SqlDataAdapter da = null;
            SqlConnection sc = GetConnection(strConnect);
            try
            {
                if (strSQL.Trim() == "")
                    da = new SqlDataAdapter("select * from " + strName, sc);
                else
                    da = new SqlDataAdapter(strSQL, sc);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                TransactionError(strConnect, "获取账套表:" + strSQL, e);
                //throw new MyException(strSQL + GetLanguage(119), e);//"获取数据报错!"
            }
            finally
            {
                da.Dispose();
                sc.Close();
            }
            return dt;
        }

        public string ExcuteSQL(string strSQL)
        {
            if (strSQL == "")
            {
                return GetLanguage(389); //"您当前操作的窗体没有内容被更新!";
            }
            char cSpliter = ';';
            string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;

            try
            {
                foreach (string str in sl)
                {
                    //SqlCommand cd = new SqlCommand(str, sc);
                    if (str == "") continue;
                    cd.CommandType = CommandType.Text;
                    cd.CommandText = str;
                    cd.ExecuteNonQuery();
                }
                strRet = "OK";
                st.Commit();
            }
            catch (Exception e)
            {
                strRet = e.Message;
                st.Rollback();
                TransactionError(sc.ConnectionString, "更新基础表:" + strSQL, e);
                //throw new MyException(strRet + "\r\n" + strSQL + "更新数据表报错!", e);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string ExcuteSQL(string strSQL, out int iCount)
        {
            iCount = 0;
            if (strSQL == "")
            {
                return ""; //"您当前操作的窗体没有内容被更新!";
            }
            //char cSpliter = ';';
            //string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = GetMyConnection();
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;

            try
            {
                //foreach (string str in sl)
                //{
                //    //SqlCommand cd = new SqlCommand(str, sc);
                //    if (str == "") continue;
                cd.CommandType = CommandType.Text;
                cd.CommandText = strSQL;
                iCount = cd.ExecuteNonQuery();
                //}
                strRet = "OK";
                st.Commit();
            }
            catch (Exception e)
            {
                st.Rollback();
                strRet = e.Message;
                SqlConnection.ClearAllPools();
                //InsertError("ExcuteSQL", e);
                //MyErrorLog.WinLog(e.Message + "\r\n(" + strSQL + GetLanguage(329) + ")");
                //throw new MyException(strSQL + GetLanguage(329), e);//更新数据表报错!
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string ExcuteSQL(string strSQL, string strConnect)
        {
            if (strSQL == "")
            {
                return GetLanguage(357); //"您当前操作的窗体没有内容被更新！";
            }
            char cSpliter = ';';
            string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = GetConnection(strConnect);
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;

            try
            {
                foreach (string str in sl)
                {
                    //SqlCommand cd = new SqlCommand(str, sc);
                    if (str == "") continue;
                    cd.CommandType = CommandType.Text;
                    cd.CommandText = str;
                    cd.ExecuteNonQuery();
                }
                strRet = "OK";
                st.Commit();
            }
            catch (Exception e)
            {
                //st.Rollback();
                strRet = e.Message;
                TransactionError(strConnect, "更新帐套表:" + strSQL, e);
                //throw new MyException(strSQL + GetLanguage(329), e);//更新数据表报错!
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        public string ExcuteSQLFile(string sqlFileName, string strConnect)
        {
            if (!File.Exists(sqlFileName)) return GetLanguage(80);
            string strRet = "";
            SqlConnection sc = GetConnection(strConnect);
            SqlTransaction st = sc.BeginTransaction();
            SqlCommand cd = new SqlCommand();
            cd.Connection = sc;
            //SqlCommand cd = sc.CreateCommand();
            cd.Transaction = st;
            try
            {
                using (FileStream stream = new FileStream(sqlFileName, FileMode.Open, FileAccess.ReadWrite))
                {
                    StreamReader reader = new StreamReader(stream, Encoding.Default);
                    StringBuilder builder = new StringBuilder();
                    String strLine = "";
                    while ((strLine = reader.ReadLine()) != null)
                    {
                        if (strLine.Trim().ToUpper() != @"GO")
                        {
                            builder.AppendLine(strLine);
                        }
                        else
                        {
                            cd.CommandText = builder.ToString();
                            cd.ExecuteNonQuery();
                            builder.Remove(0, builder.Length);
                        }
                    }
                    stream.Dispose();
                    strRet = "OK";
                    st.Commit();
                }
            }
            catch (Exception e)
            {
                strRet = e.Message;
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }


        public bool HasQuoted(DataColumn dc)
        {
            bool blRet = true;
            switch (dc.DataType.ToString())
            {
                case "System.Int32":
                case "System.Decimal":
                case "System.Double":
                case "System.Int64":
                case "System.Byte[]":
                case "System.Int16":
                    blRet = false;
                    break;
                default:
                    break;
            }
            return blRet;
        }

        public string CreateDBO()
        {
            string sCommandText = "";
            string SqlStr = "ALTER PROC [dbo].[{0}]({1}) AS {2} ;";
            string sM = "";
            string sW = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {
                    sM += string.Format(",@{0} decimal(4, 1)=null", dr["ID"] + ((iCnum != 1) ? "_" + i : ""));
                }
            }
            sCommandText += string.Format(SqlStr, "InsertDatas", "@GatherNo varchar(20), @GatherDate varchar(20), @SensorID varchar(12), @ReceiverId int, @X decimal(10, 6)=null, @Y decimal(10, 6)=nul" + sM, "insert into GatherData(GatherNo,GatherDate,SensorID,ReceiverId,X,Y," + string.Format(PublicFunc.dbo_Measure, "", "") + ") values(@GatherNo,@GatherDate,@SensorID,@ReceiverId,@X,@Y," + string.Format(PublicFunc.dbo_Measure, "@", "") + ")");
            sW = sM = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {

                    sM += string.Format(",@{0}W decimal(4, 1)=null", dr["ID"] + ((iCnum != 1) ? "_" + i : ""));
                    sW += string.Format(",{0}W=isnull(@{0}W,{0}W)", dr["ID"] + ((iCnum != 1) ? "_" + i : ""));
                }
            }
            sCommandText += string.Format(SqlStr, "UpdateDatas", "@WarnType varchar(20)" + sM + ",@ID varchar(12)", "update GatherData set WarnType=@WarnType" + sW + " where ID=@ID");
            sW = sM = "";
            foreach (DataRow dr in dtMeasureType.Rows)
            {
                int iCnum = Convert.ToInt32(dr["Cnum"]);
                for (int i = 1; i < iCnum + 1; i++)
                {
                    string sCnum = (iCnum != 1) ? "_" + i : "";
                    sM += string.Format(",@Last{0} decimal(4, 1)=null", dr["ID"] + sCnum);
                    sW += string.Format(",Last{0}=isnull(@Last{0},Last{0})", dr["ID"] + sCnum);
                }
            }
            sCommandText += string.Format(SqlStr, "SensorXYs", "@GatherNo varchar(20)=null, @X decimal(10, 6)=null, @Y decimal(10, 6)=null" + sM + ",@W varchar(20)=null,@ID varchar(12)", "update SensorXY set GatherNo =isnull(@GatherNo,GatherNo), X=isnull(@X,X),Y=isnull(@Y,Y)" + sW + ",W=isnull(@W,W) where ID=@ID");

            return ExcuteSQL(sCommandText);
        }

        /// <summary>
        /// 事务错误日志
        /// </summary>
        /// <param name="sConnect"></param>
        /// <param name="sErrorPage"></param>
        /// <param name="e"></param>
        public void TransactionError(string sConnect, string sErrorPage, Exception e)
        {
            string sMsg = string.Format("[(Name:{0}),(Connect:{1}),(Message:{2}),(Source:{3}),(StackTrace:{4})]", sErrorPage, sConnect, e.Message, e.Source, e.StackTrace); ;
            AmateBussiness.ToLog("TransactionError", DateTime.Now.ToString(), sMsg);

            //int iCount = 0;
            //string sql = "Insert into Sys_ErrorLogs(ErrorPage,ErrorMessage,ErrorSource,ErrorStackTrace,ErrorDate)Values('{0}','{1}','{2}','{3}','{4}')";
            //ExcuteSQL(string.Format(sql, sErrorPage, e.Message, e.Source, e.StackTrace, PublicFunc.GetUniformDateString(DateTime.Now)), out iCount);
        }

    }

}
